import requests
import pandas as pd
from datetime import datetime, timedelta
import time
import logging
import json
import hmac
import hashlib
import base64
from urllib.parse import urlencode
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import asyncio

#计算 gate 新上 现货 八周内价格 走势
class GateNewListingAnalyzer:
    def __init__(self, api_key=None, api_secret=None, proxy=None):
        # 初始化日志
        logging.basicConfig(
            level=logging.INFO,
            format='%(asctime)s - %(levelname)s - %(message)s'
        )
        self.logger = logging.getLogger(__name__)
        
        # API配置
        self.base_url = "https://api.gateio.ws/api/v4"
        self.api_key = api_key
        self.api_secret = api_secret
        self.proxies = None
        if proxy:
            self.proxies = {
                "http": proxy,
                "https": proxy
            }
    
    def _generate_signature(self, method, url, query_string='', body=''):
        """
        生成API签名 (只有在需要身份验证的接口中使用)
        """
        if not self.api_key or not self.api_secret:
            return {}, ''
            
        timestamp = str(int(time.time()))
        hashed_payload = hashlib.sha512(body.encode()).hexdigest()
        
        string_to_sign = '\n'.join([method, url, query_string, hashed_payload, timestamp])
        signature = hmac.new(self.api_secret.encode(), string_to_sign.encode(), hashlib.sha512).hexdigest()
        
        headers = {
            'KEY': self.api_key,
            'SIGN': signature,
            'Timestamp': timestamp,
            'Content-Type': 'application/json'
        }
        
        return headers, hashed_payload
    
    def _request(self, method, endpoint, params=None, data=None):
        """
        发送请求到Gate.io API
        """
        url = f"{self.base_url}{endpoint}"
        query_string = ''
        body = ''
        
        if params:
            query_string = urlencode(params)
            url = f"{url}?{query_string}"
        
        if data:
            body = json.dumps(data)
        
        headers, _ = self._generate_signature(method, endpoint, query_string, body)
        
        try:
            response = requests.request(
                method=method,
                url=url,
                headers=headers,
                data=body if data else None,
                proxies=self.proxies
            )
            
            response.raise_for_status()
            return response.json()
        except requests.exceptions.HTTPError as e:
            self.logger.error(f"HTTP错误: {e}")
            if response.text:
                self.logger.error(f"API响应: {response.text}")
            return None
        except Exception as e:
            self.logger.error(f"请求错误: {str(e)}")
            return None
    
    async def get_first_kline_time(self, currency_pair):
        """
        使用三个月周期的试探机制获取交易对的第一条K线数据时间
        """
        try:
            # 定义时间参数
            current_time = int(time.time())
            # 从5年前开始查找
            earliest_possible_time = int((datetime.now() - timedelta(days=1*365)).timestamp())
            
            # 使用三个月为一个周期
            time_interval = 90 * 24 * 3600  # 90天的秒数
            
            # 初始区间：从最早可能时间开始，一直到当前
            start_time = earliest_possible_time
            
            # 存储上一个有数据的周期起始时间，初始为None
            last_data_period_start = None
            
            self.logger.info(f"开始以三个月为周期查找 {currency_pair} 的第一条K线数据...")
            
            # 按时间周期从早到晚搜索
            while start_time < current_time:
                period_end = min(start_time + time_interval, current_time)
                
                # 记录本次查询的时间范围
                self.logger.info(f"请求K线数据参数: from={datetime.fromtimestamp(start_time).strftime('%Y-%m-%d')}, to={datetime.fromtimestamp(period_end).strftime('%Y-%m-%d')}")
                
                params = {
                    'currency_pair': currency_pair,
                    'interval': '1d',  # 使用日K线
                    'from': start_time,
                    'to': period_end,
                    'limit': 30  # 每个月约30天
                }
                
                candles = self._request('GET', '/spot/candlesticks', params=params)
                
                if candles and len(candles) > 0:
                    # 找到了有数据的时间段，记录该周期起始时间
                    last_data_period_start = start_time
                    
                    # 排序以确保按时间顺序
                    candles.sort(key=lambda x: int(x[0]))
                    
                    # 获取该时间段内最早的K线时间
                    first_kline_in_period = int(candles[0][0])
                    
                    list_date = datetime.fromtimestamp(first_kline_in_period)
                    self.logger.info(f"在 {datetime.fromtimestamp(start_time).strftime('%Y-%m-%d')} 到 {datetime.fromtimestamp(period_end).strftime('%Y-%m-%d')} 期间找到K线数据，最早K线时间: {list_date.strftime('%Y-%m-%d')}")
                    
                    # 如果是搜索的第一个周期就有数据，继续向前搜索
                    if start_time == earliest_possible_time:
                        # 向前移动时间段，继续搜索更早的数据
                        start_time = period_end
                    else:
                        # 已找到第一个有数据的时间段，返回该段内最早的K线时间
                        return first_kline_in_period
                else:
                    # 当前时间段没有数据
                    self.logger.info(f"在 {datetime.fromtimestamp(start_time).strftime('%Y-%m-%d')} 到 {datetime.fromtimestamp(period_end).strftime('%Y-%m-%d')} 期间没有找到K线数据")
                    
                    # 如果之前已经找到过有数据的时间段，说明现在已经到了没有数据的时间段
                    # 返回前一个有数据时间段的第一条K线时间
                    if last_data_period_start is not None:
                        # 再次查询前一个时间段，获取具体的第一条K线
                        params = {
                            'currency_pair': currency_pair,
                            'interval': '1d',
                            'from': last_data_period_start,
                            'to': last_data_period_start + time_interval,
                            'limit': 100
                        }
                        
                        self.logger.info(f"再次查询确认时间段: from={datetime.fromtimestamp(last_data_period_start).strftime('%Y-%m-%d')}, to={datetime.fromtimestamp(last_data_period_start + time_interval).strftime('%Y-%m-%d')}")
                        
                        candles = self._request('GET', '/spot/candlesticks', params=params)
                        
                        if candles and len(candles) > 0:
                            # 排序找出最早的K线
                            candles.sort(key=lambda x: int(x[0]))
                            first_kline_time = int(candles[0][0])
                            
                            list_date = datetime.fromtimestamp(first_kline_time)
                            self.logger.info(f"{currency_pair} 的第一条K线时间: {list_date.strftime('%Y-%m-%d %H:%M:%S')}")
                            
                            return first_kline_time
                        else:
                            self.logger.warning(f"再次查询确认时间段未能获取数据，可能存在数据异常")
                            return None
                    
                    # 向后移动时间段
                    start_time = period_end
                
                # 添加延时避免触发频率限制
                await asyncio.sleep(0.5)
            
            # 如果搜索到当前时间仍然没有找到数据，检查是否有记录的上一个有数据周期
            if last_data_period_start is not None:
                # 再次查询最后一个有数据的时间段
                params = {
                    'currency_pair': currency_pair,
                    'interval': '1d',
                    'from': last_data_period_start,
                    'to': last_data_period_start + time_interval,
                    'limit': 100
                }
                
                self.logger.info(f"查询最后记录的有数据时间段: from={datetime.fromtimestamp(last_data_period_start).strftime('%Y-%m-%d')}, to={datetime.fromtimestamp(last_data_period_start + time_interval).strftime('%Y-%m-%d')}")
                
                candles = self._request('GET', '/spot/candlesticks', params=params)
                
                if candles and len(candles) > 0:
                    # 排序找出最早的K线
                    candles.sort(key=lambda x: int(x[0]))
                    first_kline_time = int(candles[0][0])
                    
                    list_date = datetime.fromtimestamp(first_kline_time)
                    self.logger.info(f"{currency_pair} 的第一条K线时间: {list_date.strftime('%Y-%m-%d %H:%M:%S')}")
                    
                    return first_kline_time
            
            self.logger.warning(f"{currency_pair} 没有找到任何K线数据")
            return None
            
        except Exception as e:
            self.logger.error(f"获取 {currency_pair} 首个K线时间失败: {str(e)}")
            import traceback
            self.logger.error(traceback.format_exc())
            return None
    
    async def get_new_listings(self, start_date, end_date=None):
        """
        获取指定时间段内新上市的交易对
        """
        try:
            # 如果未指定结束日期，使用当前日期
            if not end_date:
                end_date = datetime.now().strftime('%Y-%m-%d')
                
            start_timestamp = int(datetime.strptime(start_date, '%Y-%m-%d').timestamp())
            end_timestamp = int(datetime.strptime(end_date, '%Y-%m-%d').timestamp()) + 86400  # 加一天以包含结束日期
            
            self.logger.info(f"开始时间戳: {start_timestamp}, 结束时间戳: {end_timestamp}")
            
            # 获取所有现货交易对
            currency_pairs = self._request('GET', '/spot/currency_pairs')
            
            if not currency_pairs:
                self.logger.error("获取交易对列表失败")
                return []
                
            self.logger.info(f"成功获取交易对列表，共 {len(currency_pairs)} 个")
            
            # 筛选USDT交易对
            usdt_pairs = [pair for pair in currency_pairs if pair['id'].endswith('_USDT')]
            self.logger.info(f"找到 {len(usdt_pairs)} 个USDT交易对")
            
            new_listings = []
            no_kline_data = 0
            
            # 设置进度显示
            total_pairs = len(usdt_pairs)
            processed = 0
            
            for pair in usdt_pairs:
                processed += 1
                if processed % 10 == 0 or processed == total_pairs:
                    self.logger.info(f"处理进度: {processed}/{total_pairs} ({processed/total_pairs*100:.1f}%)")
                
                # 获取交易对首个K线时间
                # if 'BLUE' not in pair['id']:
                #     continue
                first_kline_time = await self.get_first_kline_time(pair['id'])
                # first_kline_time = await self.get_first_kline_time('IP_USDT')

                if not first_kline_time:
                    no_kline_data += 1
                    continue
                
                # 检查首个K线时间是否在指定范围内
                if start_timestamp <= first_kline_time <= end_timestamp:
                    # 构造标准化的交易对符号
                    base = pair['id'].split('_')[0]
                    quote = 'USDT'
                    symbol = f"{base}/{quote}"
                    
                    new_listings.append({
                        'symbol': symbol,
                        'list_time': first_kline_time * 1000,  # 转换为毫秒
                        'base': base,
                        'quote': quote,
                        'raw_symbol': pair['id']  # 原始符号用于API调用
                    })
                    
                    list_date = datetime.fromtimestamp(first_kline_time)
                    self.logger.info(f"找到新上市交易对: {symbol}, 上市时间: {list_date.strftime('%Y-%m-%d')}")
                
                # 添加延时避免触发频率限制
                await asyncio.sleep(0.5)
            
            self.logger.info(f"没有K线数据的交易对: {no_kline_data}个")
            
            # 按上市时间排序
            new_listings.sort(key=lambda x: x['list_time'])
            self.logger.info(f"找到 {len(new_listings)} 个在指定时间范围内上市的交易对")
            
            return new_listings
            
        except Exception as e:
            self.logger.error(f"获取新上市交易对失败: {str(e)}")
            import traceback
            self.logger.error(traceback.format_exc())
            return []
    
    async def analyze_price_history(self, listing):
        """
        分析交易对的价格历史
        """
        try:
            symbol = listing['symbol']
            raw_symbol = listing['raw_symbol']
            list_time = listing['list_time'] // 1000  # 转换为秒
            
            # 定义时间点
            list_date = datetime.fromtimestamp(list_time)
            # 扩展到8周
            week_dates = [
                list_date + timedelta(days=7),    # 第1周
                list_date + timedelta(days=14),   # 第2周
                list_date + timedelta(days=21),   # 第3周
                list_date + timedelta(days=28),   # 第4周
                list_date + timedelta(days=35),   # 第5周
                list_date + timedelta(days=42),   # 第6周
                list_date + timedelta(days=49),   # 第7周
                list_date + timedelta(days=56),   # 第8周
            ]
            
            # 获取上市初始价格 - 使用上市当天的日K线开盘价
            params = {
                'currency_pair': raw_symbol,
                'interval': '1d',
                'from': list_time,
                'to': list_time + 86400,  # 上市当天
                'limit': 1
            }
            
            initial_candles = self._request('GET', '/spot/candlesticks', params=params)
            
            if not initial_candles or len(initial_candles) == 0:
                self.logger.warning(f"获取 {symbol} 初始价格失败")
                return None
                
            initial_price = float(initial_candles[0][5])  # 开盘价在索引1
            
            # 获取每周价格
            prices = {
                'symbol': symbol,
                'list_time': list_date.strftime('%Y-%m-%d %H:%M:%S'),
                'initial_price': initial_price
            }
            
            # 获取每周收盘价 - 扩展到8周
            for i, date in enumerate(week_dates, 1):
                try:
                    date_timestamp = int(date.timestamp())
                    
                    # 获取当天的K线数据
                    params = {
                        'currency_pair': raw_symbol,
                        'interval': '1d',
                        'from': date_timestamp - 86400,  # 前一天
                        'to': date_timestamp + 86400,    # 后一天
                        'limit': 3
                    }
                    
                    # 在请求K线数据前增加日志输出
                    self.logger.info(f"请求第{i}周K线数据: from={datetime.fromtimestamp(date_timestamp - 86400).strftime('%Y-%m-%d')}, to={datetime.fromtimestamp(date_timestamp + 86400).strftime('%Y-%m-%d')}")
                    
                    candles = self._request('GET', '/spot/candlesticks', params=params)
                    
                    if candles and len(candles) > 0:
                        # 找到最接近目标日期的K线
                        closest_candle = min(candles, key=lambda x: abs(int(x[0]) - date_timestamp))
                        close_price = float(closest_candle[2])  # 收盘价在索引2
                        
                        prices[f'week{i}_price'] = close_price
                        prices[f'week{i}_change'] = ((close_price - initial_price) / initial_price) * 100
                    else:
                        self.logger.warning(f"获取 {symbol} 第{i}周K线数据失败")
                        prices[f'week{i}_price'] = None
                        prices[f'week{i}_change'] = None
                        
                except Exception as e:
                    self.logger.error(f"获取 {symbol} 第{i}周价格失败: {str(e)}")
                    prices[f'week{i}_price'] = None
                    prices[f'week{i}_change'] = None
                
                # 添加延时避免触发频率限制
                await asyncio.sleep(0.5)
            
            return prices
            
        except Exception as e:
            self.logger.error(f"分析 {symbol} 价格历史失败: {str(e)}")
            return None
    
    async def run_analysis(self, start_date: str, end_date: str = None):
        """
        运行完整分析
        """
        try:
            # 获取新上市交易对
            self.logger.info(f"正在获取 {start_date} 到 {end_date or '现在'} 的新上市交易对...")
            new_listings = await self.get_new_listings(start_date, end_date)
            
            if not new_listings:
                self.logger.info("未找到新上市交易对")
                return
                
            self.logger.info(f"找到 {len(new_listings)} 个新上市交易对")
            
            # 分析每个交易对的价格历史
            results = []
            for listing in new_listings:
                self.logger.info(f"正在分析 {listing['symbol']} 的价格历史...")
                price_history = await self.analyze_price_history(listing)
                if price_history:
                    results.append(price_history)
                # 添加延时避免触发频率限制
                await asyncio.sleep(1)
            
            # 创建DataFrame并保存结果
            if results:
                df = pd.DataFrame(results)
                
                # 设置列名的中文别名 - 扩展到8周
                column_aliases = {
                    'symbol': '交易对',
                    'list_time': '上市时间',
                    'initial_price': '上市价格',
                    'week1_price': '第一周价格',
                    'week1_change': '第一周涨跌幅',
                    'week2_price': '第二周价格',
                    'week2_change': '第二周涨跌幅',
                    'week3_price': '第三周价格',
                    'week3_change': '第三周涨跌幅',
                    'week4_price': '第四周价格',
                    'week4_change': '第四周涨跌幅',
                    'week5_price': '第五周价格',
                    'week5_change': '第五周涨跌幅',
                    'week6_price': '第六周价格',
                    'week6_change': '第六周涨跌幅',
                    'week7_price': '第七周价格',
                    'week7_change': '第七周涨跌幅',
                    'week8_price': '第八周价格',
                    'week8_change': '第八周涨跌幅'
                }
                
                df = df.rename(columns=column_aliases)
                
                # 格式化数据
                for col in df.columns:
                    if '价格' in col:
                        df[col] = df[col].apply(lambda x: f"{float(x):.8f}" if x is not None else "N/A")
                    elif '涨跌幅' in col:
                        df[col] = df[col].apply(lambda x: f"{float(x):+.2f}%" if x is not None else "N/A")
                
                # 保存为Excel文件
                filename = f"gate_new_listings_{start_date}_{end_date or 'now'}.xlsx"
                
                # 创建ExcelWriter对象
                with pd.ExcelWriter(filename, engine='openpyxl') as writer:
                    df.to_excel(writer, index=False, sheet_name='新上市分析')
                    
                    # 获取工作簿和工作表
                    workbook = writer.book
                    worksheet = writer.sheets['新上市分析']
                    
                    # 设置列宽
                    for idx, col in enumerate(df.columns):
                        max_length = max(
                            df[col].astype(str).apply(len).max(),
                            len(col)
                        )
                        worksheet.column_dimensions[get_column_letter(idx + 1)].width = max_length + 2
                    
                    # 设置样式
                    header_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
                    header_font = Font(color='FFFFFF', bold=True)
                    centered_alignment = Alignment(horizontal='center', vertical='center')
                    border = Border(
                        left=Side(style='thin'),
                        right=Side(style='thin'),
                        top=Side(style='thin'),
                        bottom=Side(style='thin')
                    )
                    
                    # 应用表头样式
                    for cell in worksheet[1]:
                        cell.fill = header_fill
                        cell.font = header_font
                        cell.alignment = centered_alignment
                        cell.border = border
                    
                    # 应用数据单元格样式
                    for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row):
                        for cell in row:
                            cell.alignment = centered_alignment
                            cell.border = border
                            
                            # 为涨跌幅添加颜色
                            if '涨跌幅' in worksheet.cell(1, cell.column).value:
                                if cell.value and cell.value.startswith('+'):
                                    cell.font = Font(color='FF0000')  # 红色表示上涨
                                elif cell.value and cell.value.startswith('-'):
                                    cell.font = Font(color='00B050')  # 绿色表示下跌
                
                self.logger.info(f"分析结果已保存到 {filename}")
                
                # 打印结果预览
                print("\n新上市交易对分析结果预览:")
                print("="*100)
                print(df.to_string(index=False))
                print("="*100)
                print(f"\n完整结果已保存到: {filename}")
            
        except Exception as e:
            self.logger.error(f"运行分析失败: {str(e)}")
            import traceback
            print(traceback.format_exc())

async def main():
    # 创建分析器实例
    analyzer = GateNewListingAnalyzer(api_key=None, api_secret=None)
    
    # 指定具体的时间段 - 例如最近3个月
    start_date = (datetime.now() - timedelta(days=360)).strftime('%Y-%m-%d')
    end_date =(datetime.now() - timedelta(days=300)).strftime('%Y-%m-%d')
    
    print(f"正在分析 {start_date} 到 {end_date} 期间的新上市交易对...")
    print("注意：此过程可能需要较长时间，因为需要查询所有交易对的K线数据...")
    await analyzer.run_analysis(start_date, end_date)

if __name__ == "__main__":
    try:
        asyncio.run(main())
    except KeyboardInterrupt:
        print("\n程序已停止")
    except Exception as e:
        print(f"程序出错: {str(e)}")
